package com.glwan.fixedassetapp.utils;

import static com.glwan.fixedassetapp.common.ExcelTemplateConstant.ASSET_IMPORT_TEMPLATE_COLUMN_ARRAY;

import android.app.Activity;
import android.database.Cursor;
import android.net.Uri;
import android.os.Handler;

import com.glwan.fixedassetapp.R;
import com.glwan.fixedassetapp.common.DatabaseConstant;
import com.glwan.fixedassetapp.common.ExcelTemplateConstant;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class POIUtil {


    private static POIUtil poiUtil;
    List<List<String>> valueList = new ArrayList<>();
    List<List<String>> valueCache = new ArrayList<>();
    /**
     * 处理数据库的handle
     */
    Handler handler = new Handler(message -> {
        insertDatabaseHandle();
        return false;
    });
    private Activity activity;

    private POIUtil() {

    }

    public static POIUtil getInstance(Activity activity) {
        if (poiUtil == null) {
            poiUtil = new POIUtil();
        }
        poiUtil.activity = activity;
        return poiUtil;
    }

    /**
     * 解析Excel日期格式转换方法
     *
     * @param cell 数据对象
     * @return
     */
    public static String stringDateProcess(Cell cell) {
        String result;
        if (HSSFDateUtil.isCellDateFormatted(cell)) {// 处理日期格式、时间格式
            SimpleDateFormat sdf = null;
            if (cell.getCellStyle().getDataFormat() == HSSFDataFormat.getBuiltinFormat("h:mm")) {
                sdf = new SimpleDateFormat("HH:mm");
            } else {// 日期
//                sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                sdf = new SimpleDateFormat("yyyy-MM-dd");
            }
            Date date = cell.getDateCellValue();
            result = sdf.format(date);
        } else if (cell.getCellStyle().getDataFormat() == 58) {
            // 处理自定义日期格式：m月d日(通过判断单元格的格式id解决，id的值是58)
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            double value = cell.getNumericCellValue();
            Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
            result = sdf.format(date);
        } else {
            double value = cell.getNumericCellValue();
            CellStyle style = cell.getCellStyle();
            DecimalFormat format = new DecimalFormat();
            String temp = style.getDataFormatString();
            // 单元格设置成常规
            if (temp.equals("General")) {
                format.applyPattern("#");
            }
            result = format.format(value);
            if (result.contains(",")) {
                result = result.replaceAll(",", "");
            }


        }
        return result;
    }

    /**
     * 读取excel文件处理
     *
     * @param fileUri uri
     */
    public void readExcelFile(Uri fileUri) {

        String path = fileUri.getPath();

        if (isExcelFile(path)) {
            try (InputStream inputStream = activity.getContentResolver().openInputStream(fileUri)) {
                Workbook workbook;
                if (path.contains("xlsx")) {
                    workbook = new XSSFWorkbook(inputStream);
                } else {
                    workbook = new HSSFWorkbook(inputStream);
                }

                //只处理第一个工作表
                Sheet sheet = workbook.getSheetAt(0);

                boolean isTrue = true;

                // 遍历行
                for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {
                    Row row = sheet.getRow(i);
                    List<String> itemList = new ArrayList<>();
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    if (i == 0) {
                        //模板头部正确性校验
                        for (int j = 0; j < physicalNumberOfCells; j++) {
                            Cell cell = row.getCell(j);
                            String stringCellValue = cell.getStringCellValue();
                            if (!ExcelTemplateConstant.ASSET_IMPORT_TEMPLATE[j].equals(stringCellValue)) {
                                isTrue = false;
                                CommonUtil.openAlertDialog(activity, activity.getString(R.string.error), "模板头部错误，错误： " + stringCellValue + ",正确应为[ " + ExcelTemplateConstant.ASSET_IMPORT_TEMPLATE[j] + "]", R.drawable.error);
                                break;
                            }
                        }
                        if (!isTrue) {
                            break;
                        }
                        continue;
                    }

                    // 遍历单元格
                    for (int k = 0; k < physicalNumberOfCells; k++) {
                        Cell cell = row.getCell(k);

                        // 读取单元格的值
                        String cellValue;
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_STRING:
                            case Cell.CELL_TYPE_BLANK:
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC:
                                cellValue = stringDateProcess(cell);
                                break;
                            case Cell.CELL_TYPE_BOOLEAN:
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_FORMULA:
                                cellValue = cell.getCellFormula();
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                cellValue = String.valueOf(cell.getErrorCellValue());
                                break;
                            default:
                                cellValue = "";
                        }

                        itemList.add(cellValue);
                        if (itemList.size() + 1 == ASSET_IMPORT_TEMPLATE_COLUMN_ARRAY.size()) {
                            itemList.add("");
                        }

                    }
                    valueList.add(itemList);

                    //每100条执行一次插入数据库操作
                    if (valueList.size() >= 100) {
                        handler.sendEmptyMessage(0);
                    }
                }
                if (valueList.size() > 0) {
                    handler.sendEmptyMessage(0);
                }
                if (isTrue) {
                    CommonUtil.openAlertDialog(activity, activity.getString(R.string.tips), activity.getString(R.string.importSuccess), R.drawable.success);
                }

            } catch (IOException e) {
                e.printStackTrace();
            }

        } else {
            CommonUtil.openAlertDialog(activity, activity.getString(R.string.tips), "只允许.xls和.xlsx的格式，文件不要放到downLoad目录下", R.drawable.error);
        }

    }

    /**
     * 写入excel
     */
    public void writeExcel(Uri uri) {

        Workbook workbook = new XSSFWorkbook();

        Sheet sheet = workbook.createSheet("Sheet1");

        Row row = sheet.createRow(0);


        for (int i = 0; i < ExcelTemplateConstant.ASSET_IMPORT_TEMPLATE.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(ExcelTemplateConstant.ASSET_IMPORT_TEMPLATE[i]);
        }

        List<String> columnNameList = new ArrayList<>(ASSET_IMPORT_TEMPLATE_COLUMN_ARRAY);

        String[] columnNames = new String[columnNameList.size()];

        columnNameList.toArray(columnNames);

        Cursor cursor = DatabaseUtil.querySQL(
                DatabaseConstant.OFFLINE_INVENTORY_TASK_ITEM,
                columnNames,
                null,
                null);
        int i = 1;
        while (cursor.moveToNext()) {
            Row itemRow = sheet.createRow(i);
            for (int j = 0; j < columnNameList.size(); j++) {
                Cell cell = itemRow.createCell(j);
                cell.setCellValue(cursor.getString(j));
            }
            i++;
        }


        try (OutputStream outputStream = activity.getContentResolver().openOutputStream(uri)) {
            workbook.write(outputStream);
            CommonUtil.openAlertDialog(activity, activity.getString(R.string.tips), "导出成功", R.drawable.success);
        } catch (IOException e) {
            CommonUtil.openAlertDialog(activity, activity.getString(R.string.tips), "导出失败", R.drawable.error);
            e.printStackTrace();
        }
    }

    //插入数据库线程处理
    private void insertDatabaseHandle() {

        valueCache.addAll(valueList);

        valueList.clear();

        insertDatabase();


    }

    //插入数据库
    private void insertDatabase() {
        DatabaseUtil.batchInsert(DatabaseConstant.OFFLINE_INVENTORY_TASK_ITEM, ASSET_IMPORT_TEMPLATE_COLUMN_ARRAY, valueCache);
        valueCache.clear();
    }

    /**
     * 是否为excel格式文件
     *
     * @param filePath
     * @return
     */
    public boolean isExcelFile(String filePath) {
        File file = new File(filePath);
        String fileName = file.getName();

        if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
            return true;
        } else {
            return false;
        }
    }
}
